﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using DBUtlity;
using Models;
using System.Data;
using System.Data.SqlClient;

namespace DAL
{
    public class ProjectMainService
    {
        #region 新增
        /// <summary>
        /// 添加数据到项目主表
        /// </summary>
        /// <param name="PJMains"></param>
        /// <param name="tableName"></param>
        /// <returns></returns>
        public int InsertProjectInfo(ProjectMain PJMains, string tableName)
        {
            List<SqlParameter> param = new List<SqlParameter>();
            string sql = "insert into " + tableName;
            sql += "(WorkParentId,PJId,PJName,Mold,Maktx,MaktxName,Customer,BusinessPlate,";
            if(tableName =="PJMainsTemp")
            {
                sql += "Reason,";
                param.Add(new SqlParameter("@Reason", PJMains.Reason));
            }
            sql += "PM,StartDate,PJType,MoldType,T1EndDate,T2EndDate,T3EndDate,T4EndDate,T5EndDate)";
            sql += " values(@WorkParentId,@PJId,@PJName,@Mold,@Maktx,@MaktxName,@Customer,@BusinessPlate,";
            if (tableName == "PJMainsTemp")
            {
                sql += "@Reason,";
            }
            sql += "@PM,@StartDate,@PJType,@MoldType,@T1EndDate,@T2EndDate,@T3EndDate,@T4EndDate,@T5EndDate)";
            param.Add(new SqlParameter("@WorkParentId",PJMains.WorkParentId));
            param.Add(new SqlParameter("@PJId",PJMains.PJId));
            param.Add(new SqlParameter("@PJName",PJMains.PJName));    
            param.Add(new SqlParameter("@Mold",PJMains.Mold));
            param.Add(new SqlParameter("@Maktx",PJMains.Maktx));
            param.Add(new SqlParameter("@MaktxName",PJMains.MaktxName));
            param.Add(new SqlParameter("@Customer",PJMains.Customer));
            param.Add(new SqlParameter("@BusinessPlate",PJMains.BusinessPlate));
            param.Add(new SqlParameter("@PM",PJMains.PM));
            param.Add(new SqlParameter("@StartDate",PJMains.StartDate));
            param.Add(new SqlParameter("@PJType",PJMains.PJType));
            param.Add(new SqlParameter("@MoldType",PJMains.MoldType));
            param.Add(new SqlParameter("@T1EndDate",PJMains.T1EndDate));
            param.Add(new SqlParameter("@T2EndDate",PJMains.T2EndDate));
            param.Add(new SqlParameter("@T3EndDate",PJMains.T3EndDate));
            param.Add(new SqlParameter("@T4EndDate",PJMains.T4EndDate));
            param.Add(new SqlParameter("@T5EndDate", PJMains.T5EndDate));
            return SQLHelper.Update(sql, param.ToArray());
        }
        /// <summary>
        /// 各阶段实际完成时间（临时表）
        /// </summary>
        /// <param name="PJMain"></param>
        /// <returns></returns>
        public int InsertDateTemp(ProjectMain PJMain)
        {
            string sql = "insert into PJMainsDateTemp(WorkParentId,PJId,StageName,RealEndDate)";
            sql += " values(@WorkParentId,@PJId,@StageName,@RealEndDate)";
            SqlParameter[] param = new SqlParameter[]
            {
                new SqlParameter("@WorkParentId",PJMain.WorkParentId),
                new SqlParameter("@PJId",PJMain.PJId),
                new SqlParameter("@StageName",PJMain.StageName),
                new SqlParameter("@RealEndDate",PJMain.RealEndDate)
            };
            return SQLHelper.Update(sql, param);
        }
        #endregion

        #region 更改
        /// <summary>
        /// 根据工作流号更改项目主表数据
        /// </summary>
        /// <param name="PJMains"></param>
        /// <returns></returns>
        public int UpdateProjectInfo(ProjectMain PJMains, string tableName,string pjId)
        {
            List<SqlParameter> param = new List<SqlParameter>();
            string sql = "update " + tableName;
            sql += " set PJName=@PJname,Mold=@Mold,Maktx=@Maktx,";
            sql += "MaktxName=@MaktxName,BusinessPlate=@BusinessPlate,PM=@PM,";
            sql += "StartDate=@StartDate,PJType=@PJType,MoldType=@MoldType,";
            sql += "T1EndDate=@T1EndDate,T2EndDate=@T2EndDate,T3EndDate=@T3EndDate,";
            sql += "T4EndDate=@T4EndDate,T5EndDate=@T5EndDate,Customer=@Customer";
            if(pjId!=null)
            {
                sql += " where PJId=@PJId";
                param.Add(new SqlParameter("@PJId",pjId));
            }
            else
            {
                sql += ",Statues=@Statues,Reason=@Reason where WorkParentId=@WorkParentId";
                param.Add(new SqlParameter("@WorkParentId",PJMains.WorkParentId));
                param.Add(new SqlParameter("@Reason", PJMains.Reason));
                param.Add(new SqlParameter("@Statues", "0"));
            }
            
            param.Add(new SqlParameter("@PJName",PJMains.PJName));
            param.Add(new SqlParameter("@Mold",PJMains.Mold));
            param.Add(new SqlParameter("@Maktx",PJMains.Maktx));
            param.Add(new SqlParameter("@MaktxName",PJMains.MaktxName));
            param.Add(new SqlParameter("@BusinessPlate",PJMains.BusinessPlate));
            param.Add(new SqlParameter("@PM",PJMains.PM));
            param.Add(new SqlParameter("@StartDate",PJMains.StartDate));
            param.Add(new SqlParameter("@PJType",PJMains.PJType));
            param.Add(new SqlParameter("@MoldType",PJMains.MoldType));
            param.Add(new SqlParameter("@T1EndDate",PJMains.T1EndDate));
            param.Add(new SqlParameter("@T2EndDate",PJMains.T2EndDate));
            param.Add(new SqlParameter("@T3EndDate",PJMains.T3EndDate));
            param.Add(new SqlParameter("@T4EndDate",PJMains.T4EndDate));
            param.Add(new SqlParameter("@T5EndDate",PJMains.T5EndDate));
            param.Add(new SqlParameter("@Customer",PJMains.Customer));
                           
            return SQLHelper.Update(sql, param.ToArray());
        }
        /// <summary>
        /// 根据工作流号更改项目主表状态
        /// </summary>
        /// <param name="pjId"></param>
        /// <param name="afterStatues"></param>
        /// <returns></returns>
        public int UpdateProjectInfo(int workParentId, string afterStatues, string tableName)
        {
            string sql = "update " + tableName;
            sql += " set Statues=@Statues";
            sql += " where WorkParentId=@WorkParentId";
            SqlParameter[] param = new SqlParameter[]
            {
                new SqlParameter("@WorkParentId",workParentId),
                new SqlParameter("@Statues",afterStatues)
            };
            return SQLHelper.Update(sql, param);
        }
        /// <summary>
        /// 更新实际完成时间(主表)
        /// </summary>
        /// <param name="pjId"></param>
        /// <param name="endDate"></param>
        /// <param name="stageName"></param>
        /// <returns></returns>
        public int UpdateStage(string pjId, DateTime endDate, string stageName)
        {
            SqlParameter[] param = null;
            string sql = "update PJMains ";

            if (stageName == "T1")
            {
                sql += " set T1RealEndDate=@T1RealEndDate where PJId=@PJId";
                param = new SqlParameter[]
                {
                    new SqlParameter("@T1RealEndDate", endDate),
                    new SqlParameter("@PJId", pjId)
                };
            }
            else if (stageName == "T2")
            {
                sql += " set T2RealEndDate=@T2RealEndDate where PJId=@PJId";
                param = new SqlParameter[]
                {
                    new SqlParameter("@T2RealEndDate", endDate),
                    new SqlParameter("@PJId", pjId)
                };
            }
            else if (stageName == "T3")
            {
                sql += " set T3RealEndDate=@T3RealEndDate where PJId=@PJId";
                param = new SqlParameter[]
                {
                    new SqlParameter("@T3RealEndDate", endDate),
                    new SqlParameter("@PJId", pjId)
                };
            }
            else if (stageName == "T4")
            {
                sql += " set T4RealEndDate=@T4RealEndDate where PJId=@PJId";
                param = new SqlParameter[]
                {
                    new SqlParameter("@T4RealEndDate", endDate),
                    new SqlParameter("@PJId", pjId)
                };
            }
            else if (stageName == "T5")
            {
                sql += " set T5RealEndDate=@T5RealEndDate where PJId=@PJId";
                param = new SqlParameter[]
                {
                    new SqlParameter("@T5RealEndDate", endDate),
                    new SqlParameter("@PJId", pjId)
                };
            }
            return SQLHelper.Update(sql, param);
        }
        /// <summary>
        /// 更新DateTemp状态
        /// </summary>
        /// <param name="pjId"></param>
        /// <param name="stageName"></param>
        /// <returns></returns>
        public int UpdateStatues(string pjId,string stageName)
        {
            string sql = "update PJMainsDateTemp set Statues=@Statues";
            sql += " where PJId=@PJId and StageName=@StageName";
            SqlParameter[] param = new SqlParameter[]
            {
                new SqlParameter("@PJId",pjId),
                new SqlParameter("@Statues","1"),
                new SqlParameter("@StageName",stageName)
            };
            return SQLHelper.Update(sql, param);
        }
        /// <summary>
        /// 更新实际完成时间(临时表)
        /// </summary>
        /// <param name="PJMain"></param>
        /// <returns></returns>
        public int UpdateStage(ProjectMain PJMain)
        {
            string sql = "update PJMainsDateTemp set RealEndDate=@RealEndDate";
            sql += " where PJId=@PJId and StageName=@StageName";
            SqlParameter[] param = new SqlParameter[]
            {
                new SqlParameter("@RealEndDate",PJMain.RealEndDate),
                new SqlParameter("@PJId",PJMain.PJId),
                new SqlParameter("@StageName",PJMain.StageName)
            };
            return SQLHelper.Update(sql, param);
        }
      
        #endregion

        #region 查询
        /// <summary>
        /// 根据工作流号查询主表信息
        /// </summary>
        /// <param name="pjId"></param>
        /// <returns></returns>
        public ProjectMain GetProjectInfo(int workParentId, string pjId, string tableName)
        {
            string sql = "select * from " + tableName + " where 1=1";
            SqlParameter[] param = null;
            if (pjId == null)
            {
                sql += " and WorkParentId=@WorkParentId";
                param = new SqlParameter[]
                {
                    new SqlParameter("@WorkParentId",workParentId)
                };
            }
            else
            {
                sql += " and PJId=@PJId";
                param = new SqlParameter[]
                {
                    new SqlParameter("@PJId",pjId)
                };
            }
            SqlDataReader objReader = SQLHelper.GetReader(sql, param);
            ProjectMain ProjectMain = null;
            while (objReader.Read())
            {
                ProjectMain = new ProjectMain()
                {
                    WorkParentId = Convert.ToInt32(objReader["workParentId"]),
                    PJId = objReader["PJId"].ToString(),
                    PJName = objReader["PJName"].ToString(),
                    Mold = objReader["Mold"].ToString(),
                    Maktx = objReader["Maktx"].ToString(),
                    MaktxName = objReader["MaktxName"].ToString(),
                    Customer = objReader["Customer"].ToString(),
                    BusinessPlate = objReader["BusinessPlate"].ToString(),
                    PM = objReader["PM"].ToString(),
                    StartDate = objReader["StartDate"] is DBNull ? Convert.ToDateTime("1900-01-01") : Convert.ToDateTime(objReader["StartDate"]),
                    PJType = objReader["PJType"].ToString(),
                    MoldType = objReader["MoldType"].ToString(),
                    T1EndDate = objReader["T1EndDate"] is DBNull ? Convert.ToDateTime("1900-01-01") : Convert.ToDateTime(objReader["T1EndDate"]),
                    T2EndDate = objReader["T2EndDate"] is DBNull ? Convert.ToDateTime("1900-01-01") : Convert.ToDateTime(objReader["T2EndDate"]),
                    T3EndDate = objReader["T3EndDate"] is DBNull ? Convert.ToDateTime("1900-01-01") : Convert.ToDateTime(objReader["T3EndDate"]),
                    T4EndDate = objReader["T4EndDate"] is DBNull ? Convert.ToDateTime("1900-01-01") : Convert.ToDateTime(objReader["T4EndDate"]),
                    T5EndDate = objReader["T5EndDate"] is DBNull ? Convert.ToDateTime("1900-01-01") : Convert.ToDateTime(objReader["T5EndDate"]),
                    Statues = objReader["Statues"].ToString()
                };
                if(tableName=="PJMainsTemp")
                {
                    ProjectMain.Reason = objReader["Reason"].ToString();
                }
            }
            objReader.Close();
            return ProjectMain;
        }
        /// <summary>
        /// 查询项目主表中是否有相应状态的工作流号
        /// </summary>
        /// <param name="pjId"></param>
        /// <param name="statues"></param>
        /// <returns></returns>
        public int GetProjectInfoByStatus(int workParentId, string statues)
        {
            string sql = "select count(*) from PJMains where WorkParentId=@WorkParentId and Statues=@Statues";
            SqlParameter[] param = new SqlParameter[]
            {
                new SqlParameter("@WorkParentId",workParentId),
                new SqlParameter("@Statues",statues)
            };
            return Convert.ToInt32(SQLHelper.GetSingleResult(sql, param));
        }
        /// <summary>
        /// 查询项目主表中是否有工作流号
        /// </summary>
        /// <param name="pjId"></param>
        /// <param name="statues"></param>
        /// <returns></returns>
        public int GetProjectInfoOfWorkParentId(int workParentId,string pjId, string tableName)
        {
            string sql = "select count(*) from " + tableName;
            SqlParameter[] param = null;
            if (pjId == null)
            {
                sql += " where WorkParentId=@WorkParentId";
                param = new SqlParameter[]
                {
                    new SqlParameter("@WorkParentId",workParentId)
                };
            }
            else
            {
                sql += " where PJId=@PJId";
                param = new SqlParameter[]
                {
                    new SqlParameter("@PJId",pjId)
                };
            }
           
            return Convert.ToInt32(SQLHelper.GetSingleResult(sql, param));
        }

        /// <summary>
        /// 获取最后一行项目号
        /// </summary>
        /// <returns></returns>
        public string GetProjectLastRow()
        {
            string sql = "select PJId from PJMains where Id=(select Max(Id) from PJMains)";
            return Convert.ToString(SQLHelper.GetSingleResult(sql));
        }
        /// <summary>
        /// 获取项目号的创建者
        /// </summary>
        /// <param name="pjId"></param>
        /// <returns></returns>
        public string GetUserName(string pjId)
        {
            string sql = "select CreaterUserName from WorkflowStatuses";
            sql += " inner join PJMains on PJMains.WorkParentId = WorkflowStatuses.WorkParentId";
            sql += " where PJId=@PJId";
            SqlParameter[] param = new SqlParameter[]
            {
                new SqlParameter("@PJId",pjId)
            };
            return Convert.ToString(SQLHelper.GetSingleResult(sql, param));
        }
        /// <summary>
        /// 查询DateTemp记录
        /// </summary>
        /// <param name="workParentId"></param>
        /// <returns></returns>
        public ProjectMain GetDateTemp(int workParentId)
        {
            string sql = "select * from PJMainsDateTemp";
            sql += " where WorkParentId=@WorkParentId";
            SqlDataReader objReader = SQLHelper.GetReader(sql, new SqlParameter[] { new SqlParameter("@WorkParentId", workParentId) });
            ProjectMain PJMain = null;
            if(objReader.Read())
            {
                PJMain = new ProjectMain()
                {
                    WorkParentId = Convert.ToInt32(objReader["WorkParentId"]),
                    PJId = objReader["PJId"].ToString(),
                    StageName = objReader["StageName"].ToString(),
                    RealEndDate = Convert.ToDateTime(objReader["RealEndDate"]),
                    Statues=objReader["Statues"].ToString()
                };
            }
            objReader.Close();
            return PJMain;
        }
        /// <summary>
        /// 查询是否有重复值
        /// </summary>
        /// <param name="pjId"></param>
        /// <param name="stageName"></param>
        /// <returns></returns>
        public int GetCount(string pjId,string stageName,string statues)
        {
            List<SqlParameter> param = new List<SqlParameter>();
            string sql = "select count(*) from PJMainsDateTemp";
            sql += " where PJId=@PJId and StageName=@StageName";
            if(!string.IsNullOrEmpty(statues))
            {
                sql += " and Statues=@Statues";
                param.Add(new SqlParameter("@Statues", statues));
            }
            param.Add(new SqlParameter("@PJId", pjId));
            param.Add(new SqlParameter("@StageName", stageName));
            return Convert.ToInt32(SQLHelper.GetSingleResult(sql, param.ToArray()));
        }
        /// <summary>
        /// 查询是否有PJ号
        /// </summary>
        /// <param name="pjId"></param>
        /// <param name="stageName"></param>
        /// <returns></returns>
        public int GetCount(string pjId)
        {
            string sql = "select count(*) from PJMains where PJId=@PJId";
            return Convert.ToInt32(SQLHelper.GetSingleResult(sql, new SqlParameter[] { new SqlParameter("@PJId", pjId) }));
        }
        #endregion
    }
}
